﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dazaza.Domain.Model;
using Dazaza.Domain.Model.SearchInfo;
using Dazaza.Framework.Data;
using System.Data;
using System.Data.SqlClient;

namespace Dazaza.Repositories
{
    /// <summary>
    /// [info] table
    /// </summary>
    public class InfoRepository
    {
        /// <summary>
        /// get hot story
        /// [info] key:infoid
        /// [info] index:infoid,isanchordig
        /// [info] index:infoid,isanchordig,categoryclass
        /// [info] index:infoid,isanchordig,category
        /// [users] index:name
        /// [comment] index:infoid
        /// </summary>
        /// <param name="searchInfo"></param>
        /// <returns></returns>
        public static List<InfoModel> Search(InfoSearchInfo searchInfo)
        {
            string sql = string.Empty;
            string sqlWhere = "isanchordig=1";
            string sqlOrderBy = "infoid desc";
            string sqlCommentCount = "(select count(*) from [comment] with(nolock) where infoid = A.infoid) as commentcount_";//commentcount_区分info表中的commentcount

            // hot
            if (searchInfo.ListType == EnumInfoListType.Hot)
            {
                sqlWhere = "isanchordig=1";
            }

            // upcoming
            if (searchInfo.ListType == EnumInfoListType.Upcoming)
            {
                sqlWhere = "isanchordig=0";
            }

            // user
            if (string.IsNullOrEmpty(searchInfo.Name) == false)
            {
                sqlWhere = "name=@name and isdelete = 0";
            }

            // CategoryClass
            if (string.IsNullOrEmpty(searchInfo.CategoryClass) == false)
            {
                sqlWhere += " and categoryclass = @categoryclass ";
            }

            // Category
            if (string.IsNullOrEmpty(searchInfo.Category) == false)
            {
                sqlWhere += " and category = @category ";
            }


            string sqlCount = string.Format("select count(*) from [info] nolock where {0}", sqlWhere);

            if (searchInfo.PageIndex <= 1)
            {
                sql = string.Format(@"
                    select A.*,B.userid,B.nickname,{3} from
                    (
                        select top {0} * from [info] with(nolock)
                        where {1} order by {2}
                    ) A
                    left join [users] B with(nolock) on A.name = B.name
                    ",
                    searchInfo.PageSize,
                    sqlWhere,
                    sqlOrderBy,
                    sqlCommentCount);
            }
            else
            {
                // 使用 sql server 2k 情况下，无法使用 rownumber
                sql =
                    string.Format(@"
                    select A.*,B.userid,B.nickname,{4} from(
                                select top {0} * from [info] a with(nolock) where infoid < (select MIN(infoid) from 
                                (
	                                select top {1} infoid from [info] a with(nolock) where {2} order by {3}
                                ) S
                                ) and {2} order by {3}
                    ) A
                    left join [users] B with(nolock) on A.name = B.name                                
                    ",
                                     searchInfo.PageSize,
                                     searchInfo.PageSize * (searchInfo.PageIndex - 1),
                                     sqlWhere,
                                     sqlOrderBy,
                                     sqlCommentCount);
            }

            // get count
            DataCommand dc = new DataCommand(sqlCount, System.Data.CommandType.Text, DataBaseType.Main);
            dc.SetParameterValue("@categoryclass", System.Data.SqlDbType.NVarChar, searchInfo.CategoryClass ?? string.Empty);
            dc.SetParameterValue("@category", System.Data.SqlDbType.NVarChar, searchInfo.Category ?? string.Empty);

            searchInfo.RecordCount = dc.ExecuteScalar<int>();

            dc = new DataCommand(sql, System.Data.CommandType.Text, DataBaseType.Main);
            dc.SetParameterValue("@categoryclass", System.Data.SqlDbType.NVarChar, searchInfo.CategoryClass ?? string.Empty);
            dc.SetParameterValue("@category", System.Data.SqlDbType.NVarChar, searchInfo.Category ?? string.Empty);


            return dc.ExecuteEntityList<InfoModel>();
        }

        /// <summary>
        /// 根据Tag搜索
        /// [tags] index: infoid,tag
        /// </summary>
        /// <param name="searchInfo"></param>
        /// <returns></returns>
        public static List<InfoModel> SearchByTag(InfoSearchInfo searchInfo)
        {
            List<InfoModel> list = new List<InfoModel>();

            string sql = string.Empty;
            string sqlWhere = "tag = @tag";//dbo.[tags]
            string sqlOrderBy = "infoid desc";
            string sqlCommentCount = "(select count(*) from [comment] with(nolock) where infoid = A.infoid) as commentcount_";

            string sqlCount = string.Format("select count(*) from [tags] with(nolock) where {0}", sqlWhere);

            if (searchInfo.PageIndex <= 1)
            {
                sql = string.Format(@"
                    select I.*,B.userid,B.nickname,{3} from
                    (
                        select top {0} infoid from [tags] with(nolock)
                        where {1} order by {2}
                    ) A
                    left join [info] I with(nolock) on A.infoid = I.infoid
                    left join users B with(nolock) on I.name = B.name
                    ",
                                                  searchInfo.PageSize, sqlWhere, sqlOrderBy, sqlCommentCount);
            }
            else
            {
                sql =
                    string.Format(@"
                    select I.*,B.userid,B.nickname,{4} from(
                                select top {0} infoid from [tags] a with(nolock) where infoid < (select MIN(infoid) from 
                                (
	                                select top {1} infoid from [tags] a with(nolock) where {2} order by {3}
                                ) S
                                ) and {2} order by {3}
                    ) A
                    left join [info] I with(nolock) on A.infoid = I.infoid
                    left join [users] B with(nolock) on I.name = B.name                                
                    ",
                                     searchInfo.PageSize,
                                     searchInfo.PageSize * (searchInfo.PageIndex - 1),
                                     sqlWhere,
                                     sqlOrderBy,
                                     sqlCommentCount);
            }

            // get count
            DataCommand dc = new DataCommand(sqlCount, System.Data.CommandType.Text, DataBaseType.Main);
            dc.SetParameterValue("@tag", System.Data.SqlDbType.NVarChar, searchInfo.Tag ?? string.Empty);

            searchInfo.RecordCount = dc.ExecuteScalar<int>();

            dc = new DataCommand(sql, System.Data.CommandType.Text, DataBaseType.Main);
            dc.SetParameterValue("@tag", System.Data.SqlDbType.NVarChar, searchInfo.Tag ?? string.Empty);

            return dc.ExecuteEntityList<InfoModel>();
        }

        // explore
        public static List<InfoModel> GetExplore(int count)
        {
            string sqlWhere = "isanchordig=1";
            string sqlOrderBy = "newid()";
            string sqlCommentCount = "(select count(*) from [comment] with(nolock) where infoid = A.infoid) as commentcount_";

            string sql = string.Format(@"
                    select A.*,B.userid,B.nickname from
                    (
                        select top {0} * from [info] with(nolock)
                        where {1} order by newid()
                    ) A
                    left join [users] B with(nolock) on A.name = B.name
                    ",
                                                  count, sqlWhere, sqlOrderBy, sqlCommentCount);


            DataCommand dc = new DataCommand(sql, System.Data.CommandType.Text, DataBaseType.Main);

            return dc.ExecuteEntityList<InfoModel>();
        }

        // 用户提交的信息
        // [index] name,isdelete
        public static List<InfoModel> SearchByUserName(string userName, InfoSearchInfo searchInfo)
        {
            //如果 searchInfo 里面包含 tag，就跳转
            if (string.IsNullOrEmpty(searchInfo.Tag) == false)
            {
                return SearchByUserNameAndTag(userName, searchInfo.Tag, searchInfo);
            }

            string sql = string.Empty;
            string sqlWhere = "name=@name and isdelete = 0";
            string sqlOrderBy = "infoid desc";
            string sqlCommentCount =
                "(select count(*) from [comment] with(nolock) where infoid = A.infoid) as commentcount_";
            string sqlCount = "select count(*) from [info] nolock where " + sqlWhere;

            if (searchInfo.PageIndex <= 1)
            {
                sql = string.Format(@"
                    select A.*,B.userid,B.nickname,{3} from
                    (
                        select top {0} * from [info] with(nolock)
                        where {1} order by {2}
                    ) A
                    left join [users] B with(nolock) on A.name = B.name
                    ",
                    searchInfo.PageSize,
                    sqlWhere,
                    sqlOrderBy,
                    sqlCommentCount);
            }
            else
            {
                // 使用 sql server 2k 情况下，无法使用 rownumber
                sql =
                    string.Format(@"
                    select A.*,B.userid,B.nickname,{4} from(
                                select top {0} * from [info] a with(nolock) where infoid < (select MIN(infoid) from 
                                (
	                                select top {1} infoid from [info] a with(nolock) where {2} order by {3}
                                ) S
                                ) and {2} order by {3}
                    ) A
                    left join [users] B with(nolock) on A.name = B.name                                
                    ",
                                     searchInfo.PageSize,
                                     searchInfo.PageSize * (searchInfo.PageIndex - 1),
                                     sqlWhere,
                                     sqlOrderBy,
                                     sqlCommentCount);
            }


            return new List<InfoModel>();
        }

        // 用户提交的信息，并且按照 tag 浏览
        public static List<InfoModel> SearchByUserNameAndTag(string userName, string tag, InfoSearchInfo searchInfo)
        {
            // 如果 tag 为空，并且searchInfo的tag为空，跳转
            if (string.IsNullOrEmpty(tag)
                && string.IsNullOrEmpty(searchInfo.Tag))
            {
                return SearchByUserName(userName, searchInfo);
            }

            if (string.IsNullOrEmpty(tag))
            {
                tag = searchInfo.Tag;
            }



            return new List<InfoModel>();
        }

        //提交信息
        public static void Add(InfoModel model)
        {
            string sql = @"insert into [info] 
            (
            url,
            title,
            tags,
            name,
            posttime,
            ip,
            mark,
            click,
            commentcount,
            note,
            imagename,
            --infoid,
            abstract,
            isarticle,
            category,
            bury,
            isanchordig,
            isanchorbury,
            favoritecount,
            clickip,
            commentip,
            favoriteip,
            weight,
            ischecked,
            ishighlights,
            isdelete,
            rank,
            categoryclass
            ) 
            values
            (
            @url,
            @title,
            @tags,
            @name,
            @posttime,
            @ip,
            @mark,
            @click,
            @commentcount,
            @note,
            @imagename,
            --@infoid,
            @abstract,
            @isarticle,
            @category,
            @bury,
            @isanchordig,
            @isanchorbury,
            @favoritecount,
            @clickip,
            @commentip,
            @favoriteip,
            @weight,
            @ischecked,
            @ishighlights,
            @isdelete,
            @rank,
            @categoryclass
            )
            ";

            SqlParameter[] parameters = new SqlParameter[] 
            { 
                new SqlParameter("@url",model.Url),
            new SqlParameter("@title",model.Title),
            new SqlParameter("@tags",model.Tags),
            new SqlParameter("@name",model.Name),
            new SqlParameter("@posttime",model.Posttime),
            new SqlParameter("@ip",model.Ip),
            new SqlParameter("@mark",model.Mark),
            new SqlParameter("@click",model.Click),
            new SqlParameter("@commentcount",0),
            new SqlParameter("@note",model.Note),
            new SqlParameter("@imagename",model.ImageName),
            new SqlParameter("@infoid",model.InfoID),
            new SqlParameter("@abstract",model.Abstract),
            new SqlParameter("@isarticle",model.IsArticle),
            new SqlParameter("@category",model.Category),
            new SqlParameter("@bury",model.Bury),
            new SqlParameter("@isanchordig",model.IsAnchorDig),
            new SqlParameter("@isanchorbury",model.IsAnchorBury),
            new SqlParameter("@favoritecount",model.FavoriteCount),
            new SqlParameter("@clickip",model.ClickIP),
            new SqlParameter("@commentip",model.CommentIP),
            new SqlParameter("@favoriteip",model.FavoriteIP),
            new SqlParameter("@weight",model.Weight),
            new SqlParameter("@ischecked",model.IsChecked),
            new SqlParameter("@ishighlights",model.IsHighlights),
            new SqlParameter("@isdelete",model.IsDelete),
            new SqlParameter("@rank",model.Rank),
            new SqlParameter("@categoryclass",model.CategoryClass)
            };

            DataCommand dc = new DataCommand(sql, System.Data.CommandType.Text, DataBaseType.Main);
            dc.ExecuteNonQuery();

            return;
        }

    }
}
